
using System;
using lo=unoidl.com.sun.star;


namespace ConsoleApplication1
{

  // __________  implementation  ____________________________________

  /** Create a spreadsheet document and provide access to table contents.
   */
  public class GeneralTableSample : SpreadsheetDocHelper
  {

    public static void Main2(String[] args)
    {
      try
      {
        using( GeneralTableSample aSample =
                      new GeneralTableSample(args) )
        {
          aSample.doSampleFunction();
        }
        Console.WriteLine("Sample done.");
      }
      catch( Exception ex )
      {
        Console.WriteLine("Sample caught exception! " + ex);
      }
    }

    // ________________________________________________________________

    public GeneralTableSample(String[] args)
      : base(args)
    {
    }

    // ________________________________________________________________

    /// This sample function modifies cells and cell ranges.
    public void doSampleFunction()
    {
      // for common usage
      lo.sheet.XSpreadsheet xSheet = getSpreadsheet(0);
      lo.beans.XPropertySet xPropSet = null;
      lo.table.XCell xCell = null;
      lo.table.XCellRange xCellRange = null;

      // *** Access and modify a VALUE CELL ***
      Console.WriteLine("*** Sample for service table.Cell ***");

      xCell = xSheet.getCellByPosition(0, 0);
      // Set cell value.
      xCell.setValue(1234);

      // Get cell value.
      double nDblValue = xCell.getValue() * 2;
      xSheet.getCellByPosition(0, 1).setValue(nDblValue);

      // *** Create a FORMULA CELL and query error type ***
      xCell = xSheet.getCellByPosition(0, 2);
      // Set formula string.
      xCell.setFormula("=1/0");

      // Get error type.
      bool bValid = ( xCell.getError() == 0 );
      // Get formula string.
      String aText = "The formula " + xCell.getFormula() + " is ";
      aText += bValid ? "valid." : "erroneous.";

      // *** Insert a TEXT CELL using the XText interface ***
      xCell = xSheet.getCellByPosition(0, 3);
      lo.text.XText xCellText =
            (lo.text.XText) xCell;
      lo.text.XTextCursor xTextCursor =
            xCellText.createTextCursor();
      xCellText.insertString(xTextCursor, aText, false);

      // *** Change cell properties ***
      int nValue = bValid ? 0x00FF00 : 0xFF4040;
      xPropSet = (lo.beans.XPropertySet) xCell;
      xPropSet.setPropertyValue(
          "CellBackColor", new uno.Any((Int32) nValue));


      // *** Accessing a CELL RANGE ***
      Console.WriteLine("*** Sample for service table.CellRange ***");

      // Accessing a cell range over its position.
      xCellRange = xSheet.getCellRangeByPosition(2, 0, 3, 1);

      // Change properties of the range.
      xPropSet = (lo.beans.XPropertySet) xCellRange;
      xPropSet.setPropertyValue(
          "CellBackColor", new uno.Any((Int32) 0x8080FF));

      // Accessing a cell range over its name.
      xCellRange = xSheet.getCellRangeByName("C4:D5");

      // Change properties of the range.
      xPropSet = (lo.beans.XPropertySet) xCellRange;
      xPropSet.setPropertyValue(
          "CellBackColor", new uno.Any((Int32) 0xFFFF80));


      // *** Using the CELL CURSOR to add some data below of
      // the filled area ***
      Console.WriteLine("*** Sample for service table.CellCursor ***");

      // Create a cursor using the XSpreadsheet method createCursorByRange()
      xCellRange = xSheet.getCellRangeByName("A1");
      lo.sheet.XSheetCellRange xSheetCellRange =
            (lo.sheet.XSheetCellRange) xCellRange;

      lo.sheet.XSheetCellCursor xSheetCellCursor =
            xSheet.createCursorByRange(xSheetCellRange);
      lo.table.XCellCursor xCursor =
            (lo.table.XCellCursor) xSheetCellCursor;

      // Move to the last filled cell.
      xCursor.gotoEnd();
      // Move one row down.
      xCursor.gotoOffset(0, 1);
      xCursor.getCellByPosition(0, 0).setFormula(
          "Beyond of the last filled cell.");


      // *** Modifying COLUMNS and ROWS ***
      Console.WriteLine("*** Sample for services table.TableRows and " +
                           "table.TableColumns ***");

      lo.table.XColumnRowRange xCRRange =
            (lo.table.XColumnRowRange) xSheet;
      lo.table.XTableColumns xColumns =
            xCRRange.getColumns();
      lo.table.XTableRows xRows = xCRRange.getRows();

      // Get column C by index (interface XIndexAccess).
      uno.Any aColumnObj = xColumns.getByIndex(2);
      xPropSet = (lo.beans.XPropertySet) aColumnObj.Value;
      xPropSet.setPropertyValue("Width", new uno.Any((Int32) 5000));

      // Get the name of the column.
      lo.container.XNamed xNamed =
            (lo.container.XNamed) aColumnObj.Value;
      aText = "The name of this column is " + xNamed.getName() + ".";
      xSheet.getCellByPosition(2, 2).setFormula(aText);

      // Get column D by name (interface XNameAccess).
      lo.container.XNameAccess xColumnsName =
            (lo.container.XNameAccess) xColumns;

      aColumnObj = xColumnsName.getByName("D");
      xPropSet = (lo.beans.XPropertySet) aColumnObj.Value;
      xPropSet.setPropertyValue(
          "IsVisible", new uno.Any((Boolean) false));

      // Get row 7 by index (interface XIndexAccess)
      uno.Any aRowObj = xRows.getByIndex(6);
      xPropSet = (lo.beans.XPropertySet) aRowObj.Value;
      xPropSet.setPropertyValue("Height", new uno.Any((Int32) 5000));

      xSheet.getCellByPosition(2, 6).setFormula("What a big cell.");

      // Create a cell series with the values 1 ... 7.
      for( int nRow = 8 ; nRow < 15 ; ++nRow )
        xSheet.getCellByPosition(0, nRow).setValue(nRow - 7);
      // Insert a row between 1 and 2
      xRows.insertByIndex(9, 1);
      // Delete the rows with the values 3 and 4.
      xRows.removeByIndex(11, 2);

      // *** Inserting CHARTS ***
      Console.WriteLine("*** Sample for service table.TableCharts ***");

      lo.table.XTableChartsSupplier xChartsSupp =
            (lo.table.XTableChartsSupplier) xSheet;
      lo.table.XTableCharts xCharts =
            xChartsSupp.getCharts();

      // The chart will base on the last cell series, initializing all values.
      String aName = "newChart";
      lo.awt.Rectangle aRect =
            new lo.awt.Rectangle();
      aRect.X = 10000;
      aRect.Y = 3000;
      aRect.Width = aRect.Height = 5000;
      lo.table.CellRangeAddress[] aRanges =
            new lo.table.CellRangeAddress[1];
      aRanges[0] = createCellRangeAddress(xSheet, "A9:A14");

      // Create the chart.
      xCharts.addNewByName(aName, aRect, aRanges, false, false);

      // Get the chart by name.
      uno.Any aChartObj = xCharts.getByName(aName);
      lo.table.XTableChart xChart =
            (lo.table.XTableChart) aChartObj.Value;

      // Query the state of row and column headers.
      aText = "Chart has column headers: ";
      aText += xChart.getHasColumnHeaders() ? "yes" : "no";
      xSheet.getCellByPosition(2, 8).setFormula(aText);
      aText = "Chart has row headers: ";
      aText += xChart.getHasRowHeaders() ? "yes" : "no";
      xSheet.getCellByPosition(2, 9).setFormula(aText);
    }

  }
}
